# -*- coding = utf-8 -*-
# @Time : 2021/5/6 16:47
# @Author : 龙王赘婿_彪少
# @File : demo01.py
# @Software: PyCharm

# 导入库 Python方便，c++这里就要自己造轮子了。
import openpyxl

#打开excel
wb = openpyxl.load_workbook('example01.xlsx')

# Getting sheets from the workbook
# 列出表单
print(wb.sheetnames)

# 增加一个表单
mySheet = wb.create_sheet('mySheet')

# 列出表单
for sheet in wb:
    print(sheet.title)

# 获取表单
##Sheet1 = wb.get_sheet_by_name('Sheet1') #这个函数已经弃用了 请Use wb[sheetname]
#Sheet2 = wb['Sheet2']
SheetActive = wb.active

#打印元素 对sheet操作
print(SheetActive)
print(SheetActive['A']) #打印出cell对象
print(SheetActive['A1'])
print(SheetActive['A1'].value)  #打印出具体的值

#对cell操作
cell01 = SheetActive['A2']
print('Row {}, Column {}, is{}'.format(cell01.row, cell01.column, cell01.value))
print('Cell {}, is{}'.format(cell01.coordinate, cell01.value))
#直接行列法，表单行列是从1开始的
print(SheetActive.cell(row=3, column=1))
print(SheetActive.cell(row=3, column=1).value)

#对行列操作
#列
colA = SheetActive['A'] #列标从A开始
print(colA)
print(colA[4].value)
#行
row3 = SheetActive[1]   #行标从1开始
print(row3[0].value)
#重要，索引是从0开始的，也就是colA[0],行标是从1开始的也就是column=1
#范围
col_range = SheetActive['B:C']
row_range = SheetActive['2:4']
#再来个双重for循环就可以遍历了
for row in SheetActive.iter_rows(min_row= 1, max_row= 2, max_col= 3):
    for cell in row:
        print(cell.value)

print(tuple(SheetActive.rows))

print("-"*40)

cell_range = SheetActive['A1:D9']

for rowOfCellObjects in cell_range:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate, cellObj.value)
    print("-"*30)

#知道有多少行，多少列
print('{} * {}'.format(SheetActive.max_row, SheetActive.max_column))

#超过AA之后，把字母标转为数字
from openpyxl.utils import get_column_letter, column_index_from_string
print(get_column_letter(2),get_column_letter(47),get_column_letter(902))
print(column_index_from_string("AAH"))